Portions of this patent application contain materials that are subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure, as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights whatsoever.
Spreadsheet programs are commonly used for mathematical and database applications such as record-keeping, xe2x80x9cwhat-ifxe2x80x9d and optimization analysis, calculations, charting and graphical analysis. Electronic spreadsheets offer many advantages over their paper counterparts. For one, electronic spreadsheets allow users to manipulate more information than their paper counterparts.
Also, spreadsheet programs allow users to.perform xe2x80x9cwhat ifxe2x80x9d scenarios so that, after a set of mathematical relationships has been entered into a worksheet, the spreadsheet can be recalculated using different sets of assumptions, with the results of each recalculation appearing dynamically. Performing this operation manually, with paper and pencil, would require recalculating every relationship in the model with each change made. Thus, electronic spreadsheets offer dramatic improvements in ease of creating, editing, and using mathematical models over traditional financial modeling tools such as the accountant""s columnar pad, pencil, and calculator.
Although electronic spreadsheets are capable of manipulating vast amounts of data, there is no convenient system for placing data in the spreadsheets. Spreadsheets have cells containing specific sets of rules and instructions defined by the end user for the manipulation and presentation of data. While defining such sets of rules and instructions for each cell is an easy task, the task of directing and transferring distributed data to each cell can be quite complex.
A conventional way of doing this imports a set of desired data from a database into the spreadsheet by reading a file of the database that is stored with the data and importing it to the spreadsheet. Another conventional method includes a step of retrieving necessary data from a database to input the data into a table. For this retrieval, a programmed retrieval formula is required for the database. The former conventional method, in which a file of database is directly read, requires that the constitution of the database be understood, as well as the format of the file stored with data, and the like. The format of any table to be read is thus restricted by the storing format of the database, constituting a difficulty in a selective reading of data.
In most cases, the format of the data to be extracted from a database does not have a sufficient adaptability to the table format of an employed spreadsheet, thus requiring the user to re-form the table format of read data into an adaptive one. The latter method, in which a retrieval formula is input for database retrieval, also needs the constitution of the database to be understood, in addition to the necessary preparation of a retrieval formula by the user, who is thus required to master both how to write the formula for database retrieval and how to use a given spreadsheet.
Typically, to bind a relational database management system (RDBMS) data source to a particular cell in a spreadsheet, specific macros and coding need to be done. Writing the macros and code requires a user to have knowledge of programming in Visual Basic, Excel objects methods and properties, and Structured Query Language (SQL). Additionally, the code has to be adjusted or revamped every time the database is updated or business logic evolves. Furthermore this code is specific to one particular spreadsheet application.
In sum, a user must be familiar with details of the constitution of the database to effectively utilize data therefrom on a spreadsheet. Such a requirement puts a large burden on the user to make an effective use of the database on a spreadsheet, thus constituting a hindrance to using the spreadsheet to analyze the content of the database.
The invention binds data from a data source to one or more cells in a spreadsheet by importing a data object and defining a data source model for the data object; associating the data object with the one or more cells in the spreadsheet; and binding data from the data object to the one or more cells upon request.
Implementations of the invention include one more of the following. The importing and associating steps are performed during design time, and the binding step is performed during run time. The imported data is stored in an Extended Markup Language (XML) format. The imported object is stored in a tree. The associating step includes dragging-and-dropping one or more children of the data object into one or more cells in the spreadsheet. The data source can be a database, in which case SQL code is generated during run-time. If the data source is a business object, a method or a function associated with the data source can be invoked.
Advantages of the invention include one or more of the following. The invention provides a xe2x80x9cseamlessxe2x80x9d two-way link between the spreadsheet and an external data source such as a RDBMS, so that the two appear to be fully integrated. In such a seamless integration, the transfer of data to and from the external data source would appear to be completely automatic, occurring in the normal course of the spreadsheet""s recalculation. The invention provides a user-friendly interface with a drag-and-drop paradigm that provides the spreadsheet end user with the ability to view the entire distributed environment. Users can then simply drag-and-drop their desired distributed data source(s) to each cell of the spreadsheet. The invention thus seamlessly propagates and reflects the changes made to each cell to underlying binding data source(s) in a platform independent manner.
Moreover, distributed (or otherwise) data source(s) in a computing environment can be viewed. Such data sources can be defined or modeled into one or more data source schema(s) by simple dragging-and-dropping of entities representing data source(s). This requires no programming or technical knowledge of such concepts as SQL, or other data-source (including database) technologies on behalf of the spreadsheet user. Thus, the invention encapsulates and hides the coding and programming that would otherwise be required for the spreadsheet cell-data source binding to take place.
Other features and advantages will become apparent from the following description, including the drawings and the claims.